package com.android.publicModule.webview.db;

import java.util.ArrayList;

import com.android.publicModule.tools.Tools;
import com.android.publicModule.webview.info.UrlInfo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;

public class UrlInfoDBHelper {
	
	private static UrlInfoDBHelper urlInfoDBHelper;
	private DBOpenHelper openHelper;
	public UrlInfoDBHelper(Context context) {
		openHelper = DBOpenHelper.getInstance(context);
	}

	/**
	 * 初始化或获取实例
	 * @param context
	 * @return
	 */
	public static UrlInfoDBHelper getInstance(Context context) {
		if(urlInfoDBHelper==null)
		{
			urlInfoDBHelper = new UrlInfoDBHelper(context);
		}
		return urlInfoDBHelper;
	}
	
	/**
	 * 插入一条新的对话模式缓存数据
	 * @param info
	 * @param db
	 */
	private boolean insertUrlInfo(UrlInfo info,SQLiteDatabase db) {
		boolean result = false;
		if(info!=null)
		{
			ContentValues values = new ContentValues();
			values.put("url", info.getUrl());
			values.put("getData", info.getGetData());
			values.put("headers", info.getHeaders());
			values.put("cacheType", info.getCacheType());
			values.put("savePath", info.getSavePath());
			values.put("lastUpdateMinute", info.getLastUpdateMinute());
			values.put("updateIntervalMinute", info.getUpdateIntervalMinute());
			try {
				if(db==null||!db.isOpen())
				{
					db = openHelper.getWritableDatabase();
				}
				long insertRow = db.insert(DBOpenHelper.URL_INFO_TABLE, null, values);
				Tools.showLog("insertADCountInfo", "insertRow="+insertRow);
				result = insertRow>0;
			} catch(SQLiteConstraintException e) {
				
			} catch(SQLException e) {
				
			} catch(Exception e) {
				
			}
		}
		return result;
	}
	
	public boolean insertUrlInfo(UrlInfo info) {
		boolean result = false;
		if(info!=null)
		{
			String url = info.getUrl();
			if(url!=null&&url.length()>0)
			{
				openHelper.OpenDB();
				SQLiteDatabase db = openHelper.getWritableDatabase();
				result = insertUrlInfo(info,db);
				openHelper.closeDB(db);
			}
		}
		return result;
	}

	public int insertUrlInfo(ArrayList<UrlInfo> urlInfoList) {
		int count = 0;
		if(urlInfoList!=null)
		{
			int urlInfoListSize = urlInfoList.size();
			if(urlInfoListSize>0)
			{
				openHelper.OpenDB();
				SQLiteDatabase db = null;
				try{
					db = openHelper.getWritableDatabase();
					db.beginTransaction();
					UrlInfo info;
					String url;
					for(int i=0;i<urlInfoListSize;i++)
					{
						info = urlInfoList.get(i);
						if(info!=null)
						{
							url = info.getUrl();
							if(url!=null&&url.length()>0)
							{
								if(insertUrlInfo(info,db))
								{
									count++;
								}
							}
						}
					}
					db.setTransactionSuccessful();
				} catch(SQLiteConstraintException e) {
					
				} catch(SQLException e) {
					
				} catch(Exception e) {
					
				} finally { 
					if(db!=null)
					{
						db.endTransaction();
					}
				}
				openHelper.closeDB(db);
			}
		}
		return count;
	}
	
	public int getAllUrlInfoCount() {
		int count = 0;
		openHelper.OpenDB();
		SQLiteDatabase db = null;
		try{
			db = openHelper.getWritableDatabase();
			String []columns = {"count(*)"};
			String selection = null;
			String []selectionArgs = null;
			Cursor cursor = db.query(DBOpenHelper.URL_INFO_TABLE, columns, selection, selectionArgs, null, null,null);
			if(cursor!=null)
			{
				if(cursor.getCount()>0)
				{
					if(cursor.moveToFirst())
					{
						count = cursor.getInt(0);
					}
				}
				cursor.close();
				cursor = null;
			}
		} catch(SQLiteConstraintException e) {
			
		} catch(SQLException e) {
			
		} catch(Exception e) {
			
		}
		openHelper.closeDB(db);
		return count;
	}
	
	public int getUrlInfoCount(String url,String getData) {
		int count = 0 ;
		openHelper.OpenDB();
		SQLiteDatabase db = null;
		try {
			db = openHelper.getWritableDatabase();
			String []columns = {"count(*)"};
			String []selectionArgs = null;
			String selection = "url=?"+url;
			if(getData!=null&&getData.length()>0)
			{
				selection += " AND getData=?";
				selectionArgs = new String[] {url,getData};
			}
			else
			{
				selectionArgs = new String[] {url};
			}
			Cursor cursor = db.query(DBOpenHelper.URL_INFO_TABLE, columns, selection, selectionArgs, null, null,null);
			if(cursor!=null)
			{
				if(cursor.getCount()>0)
				{
					if(cursor.moveToFirst())
					{
						count = cursor.getInt(0);
					}
				}
				cursor.close();
				cursor = null;
			}
		} catch(SQLiteConstraintException e) {
			
		} catch(SQLException e) {
			
		} catch(Exception e) {
			
		}
		openHelper.closeDB(db);
		return count;
	}
	
	private Cursor queryUrlInfo(String url,String getData,SQLiteDatabase db) {
		Cursor cursor = null;
		try {
			if(db==null||!db.isOpen())
			{
				db = openHelper.getWritableDatabase();
			}
			String []columns = {"id","url","getData","headers","cacheType","savePath","lastUpdateMinute","updateIntervalMinute","time"};
			String []selectionArgs = null;
			String selection = "url=?"+url;
			if(getData!=null&&getData.length()>0)
			{
				selection += " AND getData=?";
				selectionArgs = new String[] {url,getData};
			}
			else
			{
				selectionArgs = new String[] {url};
			}
			cursor = db.query(DBOpenHelper.URL_INFO_TABLE, columns, selection, selectionArgs, null, null,null);
		} catch(SQLiteConstraintException e) {
			
		} catch(SQLException e) {
			
		} catch(Exception e) {
			
		}
		return cursor;
	}
	
	public ArrayList<UrlInfo> getUrlInfoList(String url,String getData) {
		ArrayList<UrlInfo> list = null;
		openHelper.OpenDB();
		SQLiteDatabase db = null;
		try {
			db = openHelper.getWritableDatabase();
			Cursor cursor = queryUrlInfo(url,getData,db);
			if(cursor!=null&&cursor.getCount()>0)
			{
				list = new ArrayList<UrlInfo>();
				UrlInfo info;
				while(cursor.moveToNext()) {
					info = new UrlInfo();
					info.setUrl(cursor.getString(1));
					info.setGetData(cursor.getString(2));
					info.setHeaders(cursor.getString(3));
					info.setCacheType(cursor.getInt(4));
					info.setSavePath(cursor.getString(5));
					info.setLastUpdateMinute(cursor.getLong(6));
					info.setUpdateIntervalMinute(cursor.getLong(7));
					list.add(info);
				}
			}
		} catch(SQLiteConstraintException e) {
			
		} catch(SQLException e) {
			
		} catch(Exception e) {
			
		}
		openHelper.closeDB(db);
		return list;
	}
	
	public UrlInfo getUrlInfo(String url,String getData) {
		UrlInfo info = null;
		openHelper.OpenDB();
		SQLiteDatabase db = null;
		try {
			db = openHelper.getWritableDatabase();
			Cursor cursor = queryUrlInfo(url,getData,db);
			if(cursor!=null&&cursor.getCount()>0)
			{
				if(cursor.moveToNext()) {
					info = new UrlInfo();
					info.setUrl(cursor.getString(1));
					info.setGetData(cursor.getString(2));
					info.setHeaders(cursor.getString(3));
					info.setCacheType(cursor.getInt(4));
					info.setSavePath(cursor.getString(5));
					info.setLastUpdateMinute(cursor.getLong(6));
					info.setUpdateIntervalMinute(cursor.getLong(7));
				}
			}
		} catch(SQLiteConstraintException e) {
			
		} catch(SQLException e) {
			
		} catch(Exception e) {
			
		}
		openHelper.closeDB(db);
		return info;
	}
	
	private int updateUrlInfo(String url,String getData,ContentValues values,SQLiteDatabase db) {
		int row = 0;
		try {
			if(db==null||!db.isOpen())
			{
				db = openHelper.getWritableDatabase();
			}
			String []whereArgs = null;
			String whereClause = "url=?"+url;
			if(getData!=null&&getData.length()>0)
			{
				whereClause += " AND getData=?";
				whereArgs = new String[] {url,getData};
			}
			else
			{
				whereArgs = new String[] {url};
			}
			row = db.update(DBOpenHelper.URL_INFO_TABLE, values, whereClause, whereArgs);
			Tools.showLog("updateUrlInfo", "row="+row);
		} catch(SQLiteConstraintException e) {
			
		} catch(SQLException e) {
			Tools.showLog("updateUrlInfo", "SQLException="+e.getMessage());
		} catch(Exception e) {
			
		}
		return row;
	}
	
	public boolean updateUrlInfo(String url,String getData,ContentValues values) {
		boolean result = false;
		openHelper.OpenDB();
		SQLiteDatabase db = null;
		try{
			db = openHelper.getWritableDatabase();
			if(url!=null&&url.length()>0&&values!=null)
			{
				result = updateUrlInfo(url,getData,values,db)>0;
			}
		} catch(SQLiteConstraintException e) {
			
		} catch(SQLException e) {
			
		} catch(Exception e) {
			
		}
		openHelper.closeDB(db);
		return result;
	}
	
	public boolean updateUrlInfo(String url,String getData,UrlInfo info) {
		boolean result = false;
		openHelper.OpenDB();
		SQLiteDatabase db = null;
		try{
			db = openHelper.getWritableDatabase();
			if(url!=null&&url.length()>0&&info!=null)
			{
				ContentValues values = new ContentValues();
				values.put("url", info.getUrl());
				values.put("getData", info.getGetData());
				values.put("headers", info.getHeaders());
				values.put("cacheType", info.getCacheType());
				values.put("savePath", info.getSavePath());
				values.put("lastUpdateMinute", info.getLastUpdateMinute());
				values.put("updateIntervalMinute", info.getUpdateIntervalMinute());
				result = updateUrlInfo(url,getData,values,db)>0;
			}
		} catch(SQLiteConstraintException e) {
			
		} catch(SQLException e) {
			
		} catch(Exception e) {
			
		}
		openHelper.closeDB(db);
		return result;
	}
	
	private int deleteUrlInfo(String url,String getData,SQLiteDatabase db) {
		int row = 0;
		try {
			if(db==null||!db.isOpen())
			{
				db = openHelper.getWritableDatabase();
			}
			String []whereArgs = null;
			String whereClause = "url=?"+url;
			if(getData!=null&&getData.length()>0)
			{
				whereClause += " AND getData=?";
				whereArgs = new String[] {url,getData};
			}
			else
			{
				whereArgs = new String[] {url};
			}
			row = db.delete(DBOpenHelper.URL_INFO_TABLE, whereClause, whereArgs);
			Tools.showLog("updateUrlInfo", "row="+row);
		} catch(SQLiteConstraintException e) {
			
		} catch(SQLException e) {
			Tools.showLog("updateUrlInfo", "SQLException="+e.getMessage());
		} catch(Exception e) {
			
		}
		return row;
	}
	
	public boolean deleteUrlInfo(String url,String getData) {
		boolean result = false;
		openHelper.OpenDB();
		SQLiteDatabase db = null;
		try{
			db = openHelper.getWritableDatabase();
			if(url!=null&&url.length()>0)
			{
				result = deleteUrlInfo(url,getData,db)>0;
			}
		} catch(SQLiteConstraintException e) {
			
		} catch(SQLException e) {
			
		} catch(Exception e) {
			
		}
		openHelper.closeDB(db);
		return result;
	}
	
	public boolean insertOrUpdateUrlInfo(UrlInfo info) {
		boolean result = false;
		if(info!=null)
		{
			String url = info.getUrl();
			if(url!=null&&url.length()>0)
			{
				String getData = info.getGetData();
				if(getUrlInfoCount(url,getData)>0)
				{
					result = updateUrlInfo(url,getData,info);
				}
				else
				{
					result = insertUrlInfo(info);
				}
			}
		}
		return result;
	}
	
}
